package com.qf.b_servlet;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

/**
 * @author zdl
 * @date 2025/1/13
 */
@WebServlet("/findAll")
public class Demo3 extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setContentType("text/html;charset=utf-8");
            List<User> users = findAll();
            for (User user : users) {
                System.out.println(user);
            }
            if (users != null){
                PrintWriter out = resp.getWriter();
                out.write("<html>");
                out.write("<head>");
                out.write("<title>全部</title>");
                out.write("</head>");
                out.write("<table border = '1'>");
                out.write("<tr>");
                out.write("<td>编号</td>");
                out.write("<td>姓名</td>");
                out.write("<td>密码</td>");
                out.write("<td>性别</td>");
                out.write("<td>余额</td>");
                out.write("<td>日期</td>");
                out.write("</tr>");
                for (User u : users) {
                    out.write("<tr>");
                    out.write("<td>"+u.getId()+"</td>");
                    out.write("<td>"+u.getUsername()+"</td>");
                    out.write("<td>"+u.getPassword()+"</td>");
                    out.write("<td>"+u.getSex()+"</td>");
                    out.write("<td>"+u.getMoney()+"</td>");
                    out.write("<td>"+u.getCreateTime()+"</td>");
                }
                out.write("</tr>");
                out.write("</table>");
                out.write("</body>");
                out.write("</html>");
            }else {
                System.out.println("<h1>无法访问</h1>");
            }

    }
    public List<User> findAll()  {
        QueryRunner qr = new QueryRunner();
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1_2?useSSL=false", "root", "123456");
            String sql = "select id,username,password,phone,create_time as createTime,money,sex from tb_user";
            List<User> userList = qr.query(conn, sql, new BeanListHandler<>(User.class));
            return userList;
        } catch (SQLException |ClassNotFoundException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

